The following CP Duplicate Record Check Formula and Duplicate Record Check Formula specify a duplicate match key using the first three digits/characters of the zip code, the first four characters of the last name (if one exists), the first character of the first name, or if the customer did not have a last name, the first five characters of the company name:
CP duplicate record check formula
UPPER(SUBSTRING(Name.ZIP,1,3) + (CASE WHEN
Name.LAST_NAME<>'' THEN (SUBSTRING(Name.LAST_NAME,1,4) +
SUBSTRING(Name.FIRST_NAME,1,1)) ELSE SUBSTRING(Name.COMPANY,1,5)
END))
Duplicate record check formula
upp(con(mid(Name.ZIP,1,3),pick(Name.LAST_NAME<>'',mid(Name.COMPANY,1,5),con(mid(Name.LAST_NAME,1,4),mid(Name.FIRST_NAME,1,1)))))
For customer Diane Block with an address of 500 Main Street, Austin, Texas 78708, the following duplicate match key would be created:
787BLOCD